# -*- coding: utf-8 -*-v
#coding=utf-8

__author__ = 'Leon'

import sys, urllib2, sqlite3, os, random, re, uuid, sys, scenicdb
from datetime import datetime
from BeautifulSoup import BeautifulSoup


HERE = os.path.dirname(os.path.abspath(__file__))
DATA_FOLDER = os.path.join(HERE, '../data')
LOGO_FOLDER = os.path.join(HERE, 'yml_logo')
DETAIL_FOLDER = os.path.join(HERE, 'yml_p_detail')

DATA_PATH = os.path.join(DATA_FOLDER, '../data/grape.db')
CHARS = 'abcdefghijklmnopqrstuvwxyz0123456789'
MARK_LIST = []


def run():
    #读取spider中所有数据
    sql = 'select * from spider'
    db = sqlite3.connect(DATA_PATH)
    for row in query_db(db, sql):
        name = row['name']
        firmName = row['firmName']
        firmUrl = row['firmUrl']
        categoryTags = row['category']
        featureTags = row['category']
        logoUrl = row['logourl']
        #pageUrl = row['firmurl']
        captionHtml = row['captionhtml']
        introHtml = row['introhtml']
        detailHtml = row['detailhtml']
        #status = row['']
        #latestUpdateTime = row['']
        #creationTime = row['']
        mark = u''.join(random.sample(CHARS, 6))

        while mark in MARK_LIST:
            mark = u''.join(random.sample(CHARS, 6))

        MARK_LIST.append(mark)

        #下载图片
        logo_download_url = 'http://www.cloudguide.com.cn/' + logoUrl
        logo_file_name = mark + logoUrl[-4:]
        local_path = os.path.join(LOGO_FOLDER, logo_file_name)
        #新建logourl文件名
        downloadimg(logo_download_url, local_path)
        #detail中的img替换?

        #下载logo->上传logo->保存url

        new_sql = 'insert into product ' \
                  '(mark,name,firmName,firmUrl,categoryTags,featureTags,logo,pageUrl,captionHtml,' \
                  'introHtml,detailHtml,status,lastUpdateTime,createTime)' \
                  ' values (:mark,:name,:firmName,:firmUrl,:categoryTags,:featureTags,:logo,:pageUrl,:captionHtml,' \
                  ':introHtml,:detailHtml,:status,:lastUpdateTime,:createTime)'

        db.execute(new_sql,
                   {'mark': mark, 'name': name, 'firmName': firmName, 'firmUrl': firmUrl, 'categoryTags': categoryTags,
                    'featureTags': featureTags, 'logo': logo_file_name, 'pageUrl': firmUrl, 'captionHtml': captionHtml,
                    'introHtml': introHtml, 'detailHtml': detailHtml,
                    'status': 0, 'lastUpdateTime': datetime.now(), 'createTime': datetime.now()})
        db.commit()
        print(u'{0} inserted'.format(name))
    db.close()


def downloadimg(url, localpath):
    data = urllib2.urlopen(url).read()
    f = file(localpath, 'wb')
    f.write(data)
    f.close()


def query_db(db, query, args=(), one=False):
    cur = db.execute(query, args)
    rv = [dict((cur.description[idx][0], value)
               for idx, value in enumerate(row)) for row in cur.fetchall()]
    return (rv[0] if rv else None) if one else rv


def parse_detail():
    reload(sys)
    sys.setdefaultencoding('utf8')

    sql = 'select * from product'
    db = sqlite3.connect(DATA_PATH)
    db.text_factory = lambda x: unicode(x, 'utf-8', 'ignore')
    for row in query_db(db, sql):
        detail_html = row['detailHtml']
        soup_detail = BeautifulSoup(detail_html)
        #page_url = row['pageUrl']
        mark = row['mark']
        id = row['id']
        name = row['name']
        #

        #替换www.cloudguide.com.cn/url.php
        for firm_tag in soup_detail.findAll('a', attrs={'href': re.compile('http://www.cloudguide.com.cn/url.php.*')}):
            firm_tag['href'] = firm_tag['href'].replace('http://www.cloudguide.com.cn/url.php?url=', '')
            firm_tag.setString('点击查看')
            #firm_tag.text = u'点击查看'
        #更新新detail到数据库
        for img_tag in soup_detail.findAll('img'):
            img_url = img_tag['src']

            img_mark = unicode(uuid.uuid1()).replace('-', '') + img_url[-4:]

            try:
                downloadimg('http://www.cloudguide.com.cn/' + img_url, os.path.join(DETAIL_FOLDER, img_mark))
            except:
                print('{0} {1} failed====url:{2}=========='.format(id, name, img_url))
                continue

            img_tag['src'] = u'http://shared.b0.upaiyun.com/yml_p_detail/' + img_mark

        update_sql = 'update product set detailHtml = :detailHtml where mark = :mark'
        db.execute(update_sql, {'detailHtml': soup_detail.prettify(),
                                'mark': mark})
        db.commit()
        print('{0} {1}================================'.format(id, name))
        #print soup_detail.prettify()
    db.close()


def parse_features():
    sql = 'select * from product'
    db = sqlite3.connect(DATA_PATH)
    tags = {}
    for row in query_db(db, sql):
        featureTags = row['featureTags']
        mark = row['mark']
        name = row['name']
        featureTags = featureTags.replace(u'云服务|', '').replace(u'云产品|', '')
        sql_update = 'update product set featureTags =:featureTags where mark =:mark'
        db.execute(sql_update, {'featureTags': featureTags, 'mark': mark})
        db.commit()

        if tags.has_key(featureTags):
            tags[featureTags] = tags[featureTags] + 1
        else:
            tags[featureTags] = 1

        print (name)

    for (k, v) in tags.iteritems():
        sql_update2 = 'insert into tag (name,count,status) values (:name,:count,:status) '
        db.execute(sql_update2, {'name': k, 'count': v, 'status': 0})
        db.commit()
        print (u'{0}  {1}'.format(k, v))

    db.close()


def upload_scenic():
    db = sqlite3.connect(DATA_PATH)
    sql = 'select * from product'
    for row in query_db(db, sql):
        id = row['id']
        mark = row['mark']
        name = row['name']
        eName = row['eName']
        firmID = row['firmID']
        firmName = row['firmName']
        firmUrl = row['firmUrl']
        categoryTags = row['categoryTags']
        featureTags = row['featureTags']
        logo = row['logo']
        pageUrl = row['pageUrl']
        captionHtml = row['captionHtml']
        introHtml = row['introHtml']
        detailHtml = row['detailHtml']
        status = row['status']
        #latestUpdateTime = row['']
        #creationTime = row['']
        #生成mysql
        querysql = u'''insert into yml_product
                   (id,mark,name,eName,firmID,firmName,firmUrl,categoryTags,featureTags,logo,pageUrl,captionHtml,
                   introHtml,detailHtml,status,lastUpdateTime,createTime)
                    values (%(id)s,%(mark)s,%(name)s,%(eName)s,%(firmID)s,%(firmName)s,%(firmUrl)s,%(categoryTags)s,
                    %(featureTags)s,%(logo)s,%(pageUrl)s,%(captionHtml)s,
                   %(introHtml)s,%(detailHtml)s,%(status)s,current_timestamp ,current_timestamp )'''
        args = {'id': id, 'mark': mark, 'name': name, 'eName': eName, 'firmID': firmID, 'firmName': firmName,
                'firmUrl': firmUrl, 'categoryTags': categoryTags, 'featureTags': featureTags, 'logo': logo,
                'pageUrl': firmUrl, 'captionHtml': captionHtml, 'introHtml': introHtml, 'detailHtml': detailHtml,
                'status': status, }
        print (row['id'])
        result = scenicdb.exesql(u'exe', querysql, args, u'http://scenicdb.duapp.com/api/exesql')
        print(result)


def upload_tags():
    sql = 'select * from product'
    db = sqlite3.connect(DATA_PATH)
    tags = {}
    for row in query_db(db, sql):
        featureTags = row['featureTags']
        mark = row['mark']
        name = row['name']
        featureTags = featureTags.replace(u'云服务|', '').replace(u'云产品|', '')
        sql_update = 'update product set featureTags =:featureTags where mark =:mark'
        db.execute(sql_update, {'featureTags': featureTags, 'mark': mark})
        db.commit()

        if tags.has_key(featureTags):
            tags[featureTags] = tags[featureTags] + 1
        else:
            tags[featureTags] = 1

        print (name)

    for (k, v) in tags.iteritems():
        sql_update2 = u'insert into yml_tag (name,count,status) values (%(name)s,%(count)s,%(status)s) '
        args = {'name': k, 'count': v, 'status': 0}
        result = scenicdb.exesql(u'exe', sql_update2, args, u'http://scenicdb.duapp.com/api/exesql')
        print(result)


# main
if __name__ == '__main__':
    print(sys.getdefaultencoding())
    reload(sys)
    sys.setdefaultencoding('utf-8')
    print(sys.getdefaultencoding())
    upload_tags()